package il.ac.tau.dbcourse.db.reports;

import il.ac.tau.dbcourse.db.annotations.SQLReport;
import il.ac.tau.dbcourse.db.Album;
import il.ac.tau.dbcourse.db.DBException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@SQLReport(column = "ALBUM_ID", table = "INCOMPLETE_ALBUM")
public class IncompleteAlbum extends AbsReport {
	public static class AlbumDetails {
		private Album album;
		private int songCount = 0;
		private int missingSongCount = 0;

		protected AlbumDetails(Album album, int songCount, int missingSongCount) {
			this.album = album;
			this.songCount = songCount;
			this.missingSongCount = missingSongCount;
		}

		public Album getAlbum() {
			return album;
		}

		public int getSongCount() {
			return songCount;
		}

		public int getMissingCount() {
			return missingSongCount;
		}
	}

	public IncompleteAlbum(Connection conn) throws SQLException {
		this.conn = conn;
		createStmt();
	}

	public static List<AlbumDetails> getAlbums(Connection conn) throws DBException {
		ArrayList<AlbumDetails> results = new ArrayList<AlbumDetails>();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn
					.prepareStatement("select incomplete_album.album_id,(select count(*) from missing_track,track where missing_track.track_id=track.id and track.album_id=incomplete_album.album_id) \"Song_count\" from incomplete_album");
			rs = stmt.executeQuery();

			while (rs.next()) {

				Album album = new Album(conn, rs.getLong(1));
				int missingSongCount = rs.getInt(2);
				int songCount = album.getTrackCount(conn);
				AlbumDetails details = new AlbumDetails(album, songCount,
						missingSongCount);
				results.add(details);
			}
			return results;
		} catch (SQLException e) {
			throw new DBException("Can't get albums - " + e.getMessage());
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}

		}
	}

}
